/********************************************/
// Creation History
// --------------------------------------------------------------
//	Company:			HTHK
//	NAME:				WeeklyException.java
//    Title   :
//	FUNCTION:
//	BY:					Beyes Xia
//	DATE:				2004-4-8 17:19
//	Update:				Poly Li
//	Data:				2004-6-7 13:43
// --------------------------------------------------------------
/*********************************************/
/*--------------------------------------------------------------
 Amend record
 Date          Version         Author          Description
 01/07/2004    1.0             Beyes           For log 1304.
 ---------------------------------------------------------------*/

package tss.weeklyexceptionreport;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;

import tss.common.DBServlet2;
import tss.common.DateUtil;
import tss.common.FloatCalculator;

import com.ffpc.sql.DataException;
import com.ffpc.struts.StrutsMessage;
import com.util.JdbcUtil;

public class WeeklyException extends DBServlet2 {
	private Logger logger = Logger.getLogger(WeeklyException.class);

	@Override
	protected String perform(HttpServletRequest request, HttpServletResponse response, StrutsMessage message) throws ServletException,
			IOException, SQLException, DataException, Exception {

		String staff_no = (request.getParameter("staff_name") == null) ? "" : request.getParameter("staff_name");
		String search_date_from = request.getParameter("date_from");
		String search_date_to = request.getParameter("date_to");

		String company = "";
		if (request.getParameter("cost_center") != null)
			company = request.getParameter("cost_center");// By Poly Li

		logger.debug("staff_no=" + staff_no);
		logger.debug("search_date_from=" + search_date_from);
		logger.debug("search_date_to=" + search_date_to);

		String begin_date = get_sunday(search_date_from);
		String end_date = get_sunday(search_date_to);
		logger.debug("begin_date:" + begin_date);
		logger.debug("end_date:" + end_date);

		Statement stmt = null;
		ResultSet rsWeek = null;
		ResultSet rsSpend = null;
		/********** add try{}catch{}finally{} 2013-07-05 by leosen *******/
		try {
			stmt = this.getConnection().createStatement();
			int weekCount = 0;
			String sqlWeek = "select to_date('" + end_date + "','YYYY-MM-DD')-to_date('" + begin_date + "','YYYY-MM-DD') from dual";
			rsWeek = stmt.executeQuery(sqlWeek);
			if (rsWeek.next()) {
				weekCount = rsWeek.getInt(1) / 7 + 1;
			}
			logger.debug("weekCount:" + weekCount);

			ArrayList week = new ArrayList();
			String tempWeek = begin_date;
			for (int i = 0; i < weekCount; i++) {
				week.add(tempWeek);
				tempWeek = add_date(tempWeek, 7);
			}
			logger.debug("week:" + week);

			ArrayList staffNo = new ArrayList();
			ArrayList staffName = new ArrayList();
			ResultSet rsStaff = null;
			String sqlStaff = " select staff_no,alias||' '||surname " + "From tss.tss_c_employee ";
			// ---------------- Modify by Beyes Xia for log 1304 7/1/2004 3:13
			// PM ----------------
			sqlStaff += "where (last_date Is Null Or trunc(last_date) > trunc(Sysdate)) ";
			// ---------------- End modify by Beyes Xia
			// -----------------------------
			if (company.length() != 0)
				sqlStaff += " and cost_center = '" + company + "' ";
			if (staff_no.length() != 0)
				sqlStaff += " and staff_no = '" + staff_no + "' ";
			sqlStaff += " order by alias";
			rsStaff = stmt.executeQuery(sqlStaff);
			while (rsStaff.next()) {
				staffNo.add(rsStaff.getString(1));
				staffName.add(rsStaff.getString(2));
			}
			logger.debug("staffNo:" + staffNo);
			logger.debug("staffName:" + staffName);

			Hashtable spend = new Hashtable();

			// ------------- Modify by Poly Li on 2004-6-7 14:10 --------//
			String sqlSpend = "Select a.staff_no,to_char(a.start_date,'YYYY/MM/DD'),"
					+ "sum(nvl(a.sunDay_spend,0)),sum(nvl(a.monday_spend,0)),sum(nvl(a.tuesday_spend,0))"
					+ ",sum(nvl(a.wednesday_spend,0)),sum(nvl(a.thursday_spend,0)),sum(nvl(a.friday_spend,0))" + ",sum(nvl(a.saturday_spend,0)) ";
			sqlSpend += " From tss.tss_t_timesheet a,tss.TSS_C_PROJECT_CATEGORY b,tss.TSS_C_PROJECT c";
			sqlSpend += " Where 1=1 ";

			sqlSpend += " and b.PROJECT_CATEGORY_NAME = c.PROJECT_CATEGORY_NAME";
			sqlSpend += " and c.PROJECT_NAME = a.PROJECT_NAME";
			if (company.length() != 0)
				sqlSpend += " and b.company_code = '" + company + "'";

			if (staff_no.length() != 0)
				sqlSpend += " And a.staff_no = '" + staff_no + "' ";
			sqlSpend += " And a.start_date >= to_date('" + begin_date + "','YYYY-MM-DD') " + " And a.start_date <= to_date('" + end_date
					+ "','YYYY-MM-DD') group by a.staff_no,a.start_date Order By a.staff_no";
			// logger.debug("begin date := "+begin_date+"  end date := "+end_date+"  sql := "+sqlSpend);

			logger.debug("WeeklyException.java -- the sqlSpend is : " + sqlSpend);
			// ---------- End modified by Poly Li ------------------//

			rsSpend = stmt.executeQuery(sqlSpend);
			
			Map<String,HashMap<String,Object>> weekMap = new HashMap<String,HashMap<String,Object>>();
			while (rsSpend.next()) {
				HashMap<String,Object> spendMap = new HashMap<String,Object>();
				int daysFrom = DateUtil.calInterval( DateUtil.toDate(rsSpend.getString(2), "yyyy/MM/dd"), DateUtil.toDate(search_date_from, "yyyy-MM-dd"),"d");
				int daysTo = DateUtil.calInterval(DateUtil.toDate(add_date(rsSpend.getString(2),6), "yyyy/MM/dd"), DateUtil.toDate(search_date_to, "yyyy-MM-dd"), "d");
				float spendTotal = 0f;
				int indexFrom = 3;
				int indexTo = 9;
				if(daysFrom >= 0){
					indexFrom = indexFrom + daysFrom;
				}
				if(daysTo < 0){
					indexTo = indexTo + daysTo;
				}
				int workDays = 0;
				for(int i = indexFrom; i <= indexTo;i++){
					workDays++;
					spendTotal = FloatCalculator.add(spendTotal, rsSpend.getFloat(i));
				}
				String[] spendArray = String.valueOf(spendTotal).split("\\.");
				if(spendArray.length == 2 && Integer.parseInt(spendArray[1]) == 0 ){
					spendMap.put("spend",spendArray[0]);
				}else{
					spendMap.put("spend",spendTotal);
				}
				if(daysFrom <= 0){
					workDays -= 1;
				}
				if(daysTo >= 0){
					workDays -= 1;
				}
				spendMap.put("flag", spendTotal >= workDays * 8);
				weekMap.put(rsSpend.getString(1) + "~" + rsSpend.getString(2), spendMap);
				
//				spend.put(rsSpend.getString(1) + "~" + rsSpend.getString(2), rsSpend.getString(3));
			}
			logger.debug("sqlSpend:" + sqlSpend);
			logger.debug("spend ht:" + spend.toString());
			request.setAttribute("week", week);
			request.setAttribute("staffNo", staffNo);
			request.setAttribute("staffName", staffName);
			request.setAttribute("spend", weekMap);
		} finally {
			JdbcUtil.release(stmt, rsWeek);
			JdbcUtil.release(stmt, rsSpend);
		}
		return "tss/weeklyexceptionreport/WeeklyExceptionReport.jsp";
	}

	private String get_sunday(String date) {
		Calendar temp = Calendar.getInstance();
		String sunday = "";
		int year = Integer.parseInt(date.substring(0, 4));
		int month = Integer.parseInt(date.substring(5, 7));
		int day = Integer.parseInt(date.substring(8, 10));
		temp.set(year, month - 1, day);
		temp.add(Calendar.DATE, 1 - temp.get(Calendar.DAY_OF_WEEK));
		String monthStr = "";
		String dayStr = "";
		monthStr = (temp.get(Calendar.MONTH) >= 9 ? "" + (temp.get(Calendar.MONTH) + 1) : "0" + (temp.get(Calendar.MONTH) + 1));
		dayStr = (temp.get(Calendar.DAY_OF_MONTH) >= 10 ? "" + temp.get(Calendar.DAY_OF_MONTH) : "0" + temp.get(Calendar.DAY_OF_MONTH));
		sunday = "" + temp.get(Calendar.YEAR) + "/" + monthStr + "/" + dayStr;
		// logger.debug("sunday="+sunday);
		return sunday;
	}

	private static String add_date(String date, int n) {
		Calendar temp = Calendar.getInstance();
		String sunday = "";
		int year = Integer.parseInt(date.substring(0, 4));
		int month = Integer.parseInt(date.substring(5, 7));
		int day = Integer.parseInt(date.substring(8, 10));
		temp.set(year, month - 1, day);
		temp.add(Calendar.DATE, n);
		String monthStr = "";
		String dayStr = "";
		monthStr = (temp.get(Calendar.MONTH) >= 9 ? "" + (temp.get(Calendar.MONTH) + 1) : "0" + (temp.get(Calendar.MONTH) + 1));
		dayStr = (temp.get(Calendar.DAY_OF_MONTH) >= 10 ? "" + temp.get(Calendar.DAY_OF_MONTH) : "0" + temp.get(Calendar.DAY_OF_MONTH));
		sunday = "" + temp.get(Calendar.YEAR) + "/" + monthStr + "/" + dayStr;
		return sunday;
	}
}
